Operators&Funtions 使用说明¶
说明¶
KVSQL是一款SQL合约执行引擎,可以运行符合范围的SQL语句,目标支持了部分常见运算符以及函数功能。以及在区块链的特殊场景下,并非所有功能都能够正常使用,部分函数被KVSQL限制以保障SQL智能合约执行的沙箱环境的安全。
限制功能主要包括如下:
- 随机数
- 部分浮点数函数
Operators¶
Name | Description |
---|---|
& | Bitwise AND |
> | Greater than operator |
>> | Right shift |
>= | Greater than or equal operator |
< | Less than operator |
<>,!= | Not equal operator |
<< | Left shift |
<= | Less than or equal operator |
<=> | NULL-safe equal to operator |
%,MOD | Modulo operator |
* | Multiplication operator |
Addition operator | |
Minus operator | |
Change the sign of the argument | |
/ | Division operator |
= | Assign a value (as part of aSETstatement, or as part of theSETclause in aUPDATEstatement) |
= | Equal operator |
^ | Bitwise XOR |
AND, && | Logical AND |
BETWEEN … AND … | Whether a value is within a range of values |
BINARY | Cast a string to a binary string |
CASE | Case operator |
DIV | Integer division |
IN() | Whether a value is within a set of values |
IS | Test a value against a boolean |
IS NOT | Test a value against a boolean |
IS NOT NULL | NOT NULL value test |
IS NULL | NULL value test |
LIKE | Simple pattern matching |
NOT,! | Negates value |
NOT BETWEEN … AND … | Whether a value is not within a range of values |
NOT IN() | Whether a value is not within a set of values |
NOT LIKE | Negation of simple pattern matching |
NOT REGEXP | Negation of REGEXP |
OR, | |
REGEXP | Whether string matches regular expression |
RLIKE | Whether string matches regular expression |
XOR | Logical XOR |
~ | Bitwise inversion |
Flow Control Functions¶
Name | Description |
---|---|
CASE | Case operator |
IF() | If/else construct |
IFNULL() | Null if/else construct |
NULLIF() | Return NULL if expr1 = expr2 |
CASE value WHEN compare_value THEN result [WHEN compare_value THEN result …] [ELSE result] END
需要注意的是,以上四种语句中的结果表达式的类型不同时,会将所有的表达式返回类型统一。这一点与mySQL存在差异。
Numeric Functions and Operators¶
Name | Description |
---|---|
%,MOD | Modulo operator |
* | Multiplication operator |
Addition operator | |
Minus operator | |
Change the sign of the argument | |
/ | Division operator |
ABS() | Return the absolute value |
CEIL() | Return the smallest integer value not less than the argument |
CEILING() | Return the smallest integer value not less than the argument |
CONV() | Convert numbers between different number bases |
CRC32() | Compute a cyclic redundancy check value |
DEGREES() | Convert radians to degrees |
DIV | Integer division |
FLOOR() | Return the largest integer value not greater than the argument |
MOD() | Return the remainder |
PI() | Return the value of pi |
RADIANS() | Return argument converted to radians |
ROUND() | Round the argument |
SIGN() | Return the sign of the argument |
SQRT() | Return the square root of the argument |
TRUNCATE() | Truncate to specified number of decimal places |
Date and Time Functions¶
Name | Description |
---|---|
CURDATE() | Return the current date |
CURRENT_DATE(),CURRENT_DATE | Synonyms for CURDATE() |
CURRENT_TIME(),CURRENT_TIME | Synonyms for CURTIME() |
CURRE NT_TIMESTAMP(),CURRENT_TIMESTAMP | Synonyms for NOW() |
CURTIME() | Return the current time |
UTC_DATE() | Return the current UTC date |
UTC_TIME() | Return the current UTC time |
UTC_TIMESTAMP() | Return the current UTC date and time |
String Functions and Operators¶
Name | Description |
---|---|
CONCAT() | Return concatenated string |
FORMAT() | Return a number formatted to specified number of decimal places |
LIKE | Simple pattern matching |
LOWER() | Return the argument in lowercase |
NOT LIKE | Negation of simple pattern matching |
NOT REGEXP | Negation of REGEXP |
REGEXP | Whether string matches regular expression |
RLIKE | Whether string matches regular expression |
TRIM() | Remove leading and trailing spaces |
UPPER() | Convert to uppercase |
Cast Functions and Operators¶
不支持json类型的转换与被转换;不支持指定字符集
Name | Description |
---|---|
BINARY | Cast a string to a binary string |
CAST() | Cast a value as a certain type |
CONVERT() | Cast a value as a certain type |
Bit Functions and Operators¶
Name | Description |
---|---|
& | Bitwise AND |
>> | Right shift |
<< | Left shift |
^ | Bitwise XOR |
BIT_COUNT() | Return the number of bits that are set |
~ | Bitwise inversion |
Aggregate Functions¶
Name | Description |
---|---|
AVG() | Return the average value of the argument |
BIT_AND() | Return bitwise AND |
BIT_OR() | Return bitwise OR |
BIT_XOR() | Return bitwise XOR |
COUNT() | Return a count of the number of rows returned |
COUNT(DISTINCT) | Return the count of a number of different values |
GROUP_CONCAT() | Return a concatenated string |
MAX() | Return the maximum value |
MIN() | Return the minimum value |
STDDEV_POP() | Return the population standard deviation |
STDDEV_SAMP() | Return the sample standard deviation |
SUM() | Return the sum |
VAR_POP() | Return the population standard variance |
VAR_SAMP() | Return the sample variance |